import copy
import os
import pymysql
from load_txt import strcture_data, get_lines



def connect_db():
    db = pymysql.connect(
        host="127.0.0.1",
        port=63306,
        user="root",
        password="123456",
        database="rec",
        charset="utf8"
    )
    print("SUCCESS : connect to database.")
    return db


def create_table_user(db):
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS user")
    sql = """CREATE TABLE user (
            USER_ID varchar(1024) NOT NULL,
            ITEMS_ID TEXT(50000),
            OUTFIT_ID varchar(1024),      
            PRIMARY KEY (USER_ID)        
             )DEFAULT CHARSET utf8"""
    cursor.execute(sql)
    print("SUCCESS : create table.")


def create_table_item(db):
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS item")
    sql = """CREATE TABLE item (
            ITEM_ID varchar(1024) NOT NULL,
            CATE_ID varchar(1024),
            PIC_URL varchar(1024),
            TITLE varchar(1024),        
            PRIMARY KEY (ITEM_ID)        
             )DEFAULT CHARSET utf8"""
    cursor.execute(sql)
    print("SUCCESS : create table.")


def create_table_outfit(db):
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS outfit")
    sql = """CREATE TABLE outfit (
            OUTFIT_ID varchar(1024) NOT NULL,
            ITEMS_ID varchar(4096),   
            PRIMARY KEY (OUTFIT_ID)        
             )DEFAULT CHARSET utf8"""
    cursor.execute(sql)
    print("SUCCESS : create table.")


def create_tables():
    db = connect_db()
    create_table_outfit(db)
    create_table_user(db)
    create_table_item(db)


def insert_row(db, table, value_str):
    cursor = db.cursor()
    sql = "INSERT INTO rec.{} VALUES {};".format(table, value_str)
    # print("SQL:{}".format(sql))
    try:
        cursor.execute(sql)
        db.commit()
        # print("SUCCESS:insert row {}".format(value_str))
        return True
    except Exception as e:
        print(e)
        print("FAILED : SQL_______{}".format(sql))
        db.rollback()
        return False


def query_by_id(db, table, id_):
    if table == "item":
        cursor = db.cursor()
        sql = "SELECT * FROM rec.{} where ITEM_ID='{}';".format(table, id_)
        print("SQL : {}".format(sql))
        try:
            cursor.execute(sql)
            results = cursor.fetchall()
            db.close()
            print("SUCCESS : query row {}".format(results))
            return results
        except Exception as e:
            print("Error : unable to fetch data --->{}".format(str(e)))
            db.close()


def rows_insert(table, num=None):
    i = get_lines(table, num)
    rows = []
    if table == "item":
        for k, v in i.items():
            row = [k]
            for _ in v:
                row.append(_)

            title = ''
            for i in v[3:]:
                title += i

            row = [k, v[0], v[1], title]
            row = tuple(row)
            rows.append(row)
        db = connect_db()
        j = 1
        for row in rows:
            insert_row(db, table, row)
            j += 1
            if j % 5000 == 0:
                print("SUCCESS : insert {} rows to table_{}".format(j, table))

    if table == "outfit" or table == "user":
        for k, v in i.items():
            if table == "outfit":
                # print("v:", v)
                row = [k, v[0]]
            if table == "user":
                row = [k, v[0], v[1]]
            row = tuple(row)
            rows.append(row)
        db = connect_db()
        j = 0
        for row in rows:
            insert_row(db, table, row)
            j += 1
            if j % 5000 == 0:
                print("SUCCESS : insert {} rows".format(j))

    print("SUCCESS : insert {} rows to table___<{}>".format(i.__len__(), table))


if __name__ == '__main__':
    # create_tables() # create example
    # results = query_by_id(db, "item", '101c7034cfd1268da179ea6a2c64639a')
    # insert_row(db, "item", rows[1])
    #create_tables()

    # print(get_lines("user",1))
    # rows_insert("item", 1)
    # create_table_user(connect_db())
    # rows_insert("user", 10000)

    # create_tables()
    rows_insert("outfit")
    rows_insert("user")
    rows_insert("item")

    # u = get_lines("user", 10)
    # i = get_lines("item", 1)
    # print(i)
    # o = get_lines("outfit", 10)

